﻿using System;
using System.Collections.Generic;
using System.Linq;

using Aden.Util.Database;
using Aden.Util.Common;
using Aden.Model.Stock;
using System.Data;
using Aden.Model.Common;
using System.Web.Http.ModelBinding;

namespace Aden.DAL.StockData
{
    public class StockTrans
    {
        private string CostCenterCode = "";

        public StockTrans(string costCenterCode)
        {
            CostCenterCode = costCenterCode;
        }


        public List<TransData> GetStock(string date)
        {
            date = date.ToStringTrim();
            if (!string.IsNullOrWhiteSpace(date))
            {
                date = $"and TRANSDATE<='{DateTime.Parse(date).ToString("yyyy-MM-dd")}'";
            }
            string sql = "select itemCode,sum(qty) stockQty from tblTransData " +
                "where isnull(IsServiceItem,0) = 0 and isnull(qty,0)<> 0 and " +
                $"CostCenterCode='{CostCenterCode}' {date} " + 
                "group by ItemCode having sum(qty) <> 0";
            return SqlServerHelper.GetEntityList<TransData>(SqlServerHelper.salesorderConn(), sql);
        }

        /// <summary>
        /// 读取库存和成本明细数据
        /// </summary>
        /// <returns></returns>
        public List<TransData> GetStockCost(string date)
        {
            try
            {
                string sql = "select a1.itemcode,a1.uom,a1.itemcost,a1.totalqty+isnull(a2.outqty,0) stockQty, " +
                   "case when (a1.runningqty+isnull(a2.outqty,0))>a1.qty then a1.qty " +    // 以前的入库足够抵扣总出库
                   "else a1.runningqty+isnull(a2.outqty,0) end qty " +                      //本次入库是最后一次抵扣总出库
                   $"{GetStockSQL(date)} order by a1.itemcode,a1.transDate,a1.createtime,a1.id";
                // 待删除
                string sqlItemNameUom = " select ItemCode, Stock_Unit as UOM, ItemName from [DM_D_DIGIMENU_STDPURCHASEPRICE] (nolock) where division='{0}';" ;

                string sqlCCdivision = $"select dbName as division from CCMast where CostCenterCode='{CostCenterCode}';";

                List<TransData> items = SqlServerHelper.GetEntityList<TransData>
                    (SqlServerHelper.salesorderConn(), sql);

                string division = SqlServerHelper.GetDataScalar(SqlServerHelper.salesorderConn(), sqlCCdivision).ToString();
                if (!string.IsNullOrWhiteSpace(division)) {
                    sqlItemNameUom = string.Format(sqlItemNameUom, division);
                }
                List<TransData> ItemNameUom = SqlServerHelper.GetEntityList<TransData>(SqlServerHelper.salesorderConn(), sqlItemNameUom);;

                #region 连续相同的cost，数量合并
                items = items.GroupBy(q => new { q.itemCode, q.uom, q.stockQty }).GroupJoin(ItemNameUom,
                    a => a.Key.itemCode.ToStringTrim(), b => b.itemCode.ToStringTrim(), (a, b) => new { a, b }).Select(q =>
                    {
                        string uom = ItemNameUom.Where(dr => dr.itemCode == q.a.Key.itemCode).FirstOrDefault() ==null? "" 
                            : ItemNameUom.Where(dr => dr.itemCode == q.a.Key.itemCode).FirstOrDefault().uom;
                        TransData d = new TransData()
                        {
                            itemCode = q.a.Key.itemCode,
                            itemName = q.b.FirstOrDefault() == null ? "" : q.b.FirstOrDefault().itemName,
                            uom = !string.IsNullOrWhiteSpace(uom)? uom : q.a.Key.uom,
                            stockQty = q.a.Key.stockQty,
                            lines = new List<TransLine>()
                        };

                        decimal xcost = 0;
                        decimal xqty = 0;
                        int x = 0;

                        d.lines.AddRange(q.a.Select(p =>
                        {
                            if (xcost != p.itemCost)
                            {
                                xqty = 0;
                                x++;
                            }

                            xqty += p.qty ?? 0;
                            xcost = p.itemCost ?? 0;

                            return new
                            {
                                index = x,
                                qty = xqty,
                                cost = xcost
                            };
                        }).GroupBy(p => new { p.index, p.cost }).Select(p => new TransLine
                        {
                            itemCost = p.Key.cost,
                            stockQty = p.Max(l => l.qty),
                        }));
                        return d;
                    }).ToList();
                #endregion
                
                return items;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        /*
         * date = null, 全部库存事务
         */
        private string GetStockSQL(string date,string itemCode = null)
        {
            try
            {
                date = date.ToStringTrim();
                if (!string.IsNullOrWhiteSpace(date))
                {
                    //date = $"and transdate<='{DateTime.Parse(date).ToString("yyyy-MM-dd")}'";
                    date = $"and convert(varchar(10),TRANSDATE,23)<='{DateTime.Parse(date).ToString("yyyy-MM-dd")}'";
                }

                if (!string.IsNullOrWhiteSpace(itemCode))
                    itemCode = $"and itemcode='{itemCode}'";

                string sql = " from " +
                    "(select itemcode,TRANSDATE,qty,isnull(itemcost,0) itemcost,createtime,id,uom,businessType," +
                    "sum(qty) over(partition by itemcode order by TRANSDATE,createtime,id) as runningqty, " + //按item，累计进货量
                    "sum(qty) over(partition by itemcode) as totalqty " +
                    "from tblTransData (nolock) " +
                    $"where isnull(IsServiceItem,0)=0 and isnull(qty,0)>0 and costcentercode='{CostCenterCode}' {date} {itemCode}) a1 " +
                    "left join (select itemcode,sum(isnull(qty,0)) outqty from tblTransData (nolock) " +
                    $"where isnull(IsServiceItem,0)=0  and isnull(qty,0)<0  and costcentercode='{CostCenterCode}' {date} {itemCode} " +
                    "group by itemcode) a2 " +
                    "on a1.itemcode=a2.itemcode " +
                    "where a1.runningqty+isnull(a2.outqty,0)>0 ";

                return sql;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public DateTime? GetLastOutDate()
        {
            //最后一个出库日的第二天
            string sql = "SELECT TOP 1 A.DATE FROM (SELECT 1 ID, MAX(TRANSDATE) DATE FROM TBLTRANSDATA (NOLOCK) "
                + " WHERE  COSTCENTERCODE = '{0}' AND QTY< 0 AND isnull(IsServiceItem,0)=0";

            //最早的入库日期
            sql += " UNION ALL " +
                " SELECT 2 ID, MIN(TRANSDATE-1) DATE FROM tblTransData (NOLOCK) " +
                " WHERE  COSTCENTERCODE = '{0}' AND QTY> 0 AND ISNULL(ISSERVICEITEM,0)=0) A " +
                " WHERE ISNULL(A.DATE,'')<>'' ORDER BY A.ID ";

            sql = string.Format(sql, CostCenterCode);
            DateTime d = DateTime.Now.AddDays(-30);
            try
            {
                var dt = SqlServerHelper.GetDataScalar(SqlServerHelper.salesorderConn(), sql);
                if (dt == null) return null;
                DateTime date = DateTime.Parse(dt.ToStringTrim());
                if (date < d) throw new Exception("");
                return date.Date;
            }
            catch
            {
                return d.Date;
            }
        }

        public string GetFirstNotInStockDate()
        {
            //最小的未入库日期
            string sql = "SELECT MIN(B.ORDERDATE) FROM TBLORDERLINES (NOLOCK) A, TBLORDERHEAD (NOLOCK) B " +
                "left join (select distinct HeadGUID from tblOrderLines " +
                "where DeleteTime is null and isnull(RemainQty,0)< (isnull(Qty, 0) + isnull(AdjQty, 0)) AND (isnull(Qty, 0) + isnull(AdjQty, 0))>0) a3 " +
                "on B.HeadGUID = a3.HeadGUID " +
                $" WHERE A.HEADGUID = B.HEADGUID AND B.COSTCENTERCODE = '{CostCenterCode}' AND a3.HeadGUID is null " +
                " AND DATEADD(D,1,B.CREATETIME)< CAST(B.ORDERDATE AS VARCHAR(10))+ ' {0}' AND B.ORDERTYPE='FOOD' AND B.ACTIVE=1 AND isnull(A.AdjQty,0)+isnull(a.Qty,0)>0"; //0采购单不属于收货单和消耗单

            return sql;
        }

        // 获取价目表最小有效日期,用于POStockIn
        public DateTime? GetMinPriceListDate(string costCenterCode)
        {
            string sql1 = " SELECT DIVISION AS COMPANY, MIN(validfrom) AS DATE FROM [dbo].[DM_D_ERP_PurchaseAgreement_NEW](nolock)  " +
                " WHERE GETDATE() BETWEEN ISNULL(VALIDFROM,'2000-01-01') AND ISNULL(VALIDTO, '2099-12-31') GROUP BY Division ";
            string sql2 = $" SELECT DBNAME AS COMPANY FROM CCMAST (NOLOCK) WHERE COSTCENTERCODE='{costCenterCode}'";

            DataTable dt1 = SqlServerHelper.GetDataTable(SqlServerHelper.purchasepriceconn(), sql1);
            DataTable dt2 = SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), sql2);

            if (dt1 == null || dt2 == null || dt1.Rows.Count == 0 || dt2.Rows.Count == 0) 
                return null;

            DateTime? date = dt1.AsEnumerable().Join(dt2.AsEnumerable(),
                mos => mos.Field<string>("company").ToStringTrim(), nos => nos.Field<string>("company"), (mos, nos) => new { mos, nos }).
                Select(dr => dr.mos.Field<DateTime?>("DATE")).First();

            return date;
        }
    }
}
